package com.esaheki.flashcards.dao;

import java.sql.*;
import java.util.ArrayList;
import com.esaheki.flashcards.classes.*;

public class UserDAO extends Conexao {
	
	public UserDAO() throws ClassNotFoundException, SQLException {
		super();
	}
	
	public void insertUser(User user) throws SQLException {
		String sql = "INSERT INTO users VALUES (NULL, ?, ?)";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, user.getName());
		stmt.setDouble(2, user.getAverageScore());
		stmt.executeUpdate();
	}
	
	public void updateUser(User user) throws SQLException {
		String sql = "UPDATE users SET name = ?, score = ? WHERE id = ?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, user.getName());
		stmt.setDouble(2, user.getAverageScore());
		stmt.setInt(3, user.getId());
		stmt.executeUpdate();
	}
	
	public void deleteUser(User user) throws SQLException {
		String sql = "DELETE cards FROM cards, categories WHERE categories.user = ? AND cards.category = categories.id";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, user.getId());
		stmt.executeUpdate();
		
		sql = "DELETE FROM categories WHERE user = ?";
		stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, user.getId());
		stmt.executeUpdate();
		
		sql = "DELETE FROM users WHERE id = ?";
		stmt = conexao.prepareStatement(sql);
		stmt.setInt(1, user.getId());
		stmt.executeUpdate();
	}
	
	public ArrayList<User> getUserList() throws SQLException {
		ArrayList<User> list = new ArrayList<User>();
		
		Statement stmt = conexao.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM users ORDER BY name");
		
		while(rs.next()) {
			User user = new User(rs.getString("name"));
			user.setId(rs.getInt("id"));
			user.setAverageScore(rs.getDouble("score"));
			list.add(user);
		}
		
		return list;
	}

}
